To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

August 28, 2007

Author

Alexey Kovyrin

Benchmarks

Insight for Developers

Share this Post:

When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one – to get a result set, another – to count total number of rows. In this post I’ll try to check, is this true or not and when it is better to run two separate queries.

For my tests I’ve created following simple table:

<br>CREATE TABLE `count_test` (<br>  `a` int(10) NOT NULL auto_increment,<br>  `b` int(10) NOT NULL,<br>  `c` int(10) NOT NULL,<br>  `d` varchar(32) NOT NULL,<br>  PRIMARY KEY  (`a`),<br>  KEY `bc` (`b`,`c`)<br>) ENGINE=MyISAM<br>

1	<br>CREATE TABLE `count_test` (<br> `a` int(10) NOT NULL auto_increment,<br> `b` int(10) NOT NULL,<br> `c` int(10) NOT NULL,<br> `d` varchar(32) NOT NULL,<br> PRIMARY KEY (`a`),<br> KEY `bc` (`b`,`c`)<br>) ENGINE=MyISAM<br>

Test data has been created with following script (which creates 10M records):

<br>mysql_connect("127.0.0.1", "root");<br>mysql_select_db("test");<br><br>for ($i = 0; $i < 10000000; $i++) {<br>    $b = $i % 1000;<br>    mysql_query("INSERT INTO count_test SET b=$b, c=ROUND(RAND()*10), d=MD5($i)");<br>}<br>

1	<br>mysql_connect("127.0.0.1", "root");<br>mysql_select_db("test");<br><br>for ($i = 0; $i < 10000000; $i++) {<br> $b = $i % 1000;<br> mysql_query("INSERT INTO count_test SET b=$b, c=ROUND(RAND()*10), d=MD5($i)");<br>}<br>

First of all, let’s try to perform some query on this table using indexed column b in where clause:

<br>mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;<br>

1	<br>mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5;<br>

Results with SQL_CALC_FOUND_ROWS are following: for each b value it takes 20-100 sec to execute uncached and 2-5 sec after warmup. Such difference could be explained by the I/O which required for this query – mysql accesses all 10k rows this query could produce without LIMIT clause.

Let’s check, how long it’d take if we’ll try to use two separate queries:

<br>mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;<br>

1	<br>mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5;<br>

The results are following: it takes 0.01-0.11 sec to run this query first time and 0.00-0.02 sec for all consecutive runs.

And now – we need too check how long our COUNT query would take:

<br>mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;<br>

1	<br>mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;<br>

Result is really impressive here: 0.00-0.04 sec for all runs.

So, as we can see, total time for SELECT+COUNT (0.00-0.15 sec) is much less than execution time for original query (2-100 sec). Let’s take a look at EXPLAINs:

<br>mysql> explain SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5;<br>+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+<br>| id | select_type | table      | type | possible_keys | key  | key_len | ref   | rows  | Extra       |<br>+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+<br>|  1 | SIMPLE      | count_test | ref  | bc            | bc   | 4       | const | 75327 | Using where | <br>+----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+<br>1 row in set (0.00 sec)<br><br>mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;<br>+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+<br>| id | select_type | table      | type | possible_keys | key  | key_len | ref   | rows | Extra       |<br>+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+<br>|  1 | SIMPLE      | count_test | ref  | bc            | bc   | 4       | const | 5479 | Using index | <br>+----+-------------+------------+------+---------------+------+---------+-------+------+-------------+<br>1 row in set (0.00 sec)<br>

mysql> explain SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5; +----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+ | 1 | SIMPLE | count_test | ref | bc | bc | 4 | const | 75327 | Using where | +----+-------------+------------+------+---------------+------+---------+-------+-------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; +----+-------------+------------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | count_test | ref | bc | bc | 4 | const | 5479 | Using index | +----+-------------+------------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec)

Here is why our count was much faster – MySQL accessed our table data when calculated result set size even when this was not needed (after the first 5 rows specified in LIMIT clause). With count(*) it used index scan inly which is much faster here.

Just to be objective I’ve tried to perform this test without indexes (full scan) and with index on b column. Results were following:

Full-scan:
- 7 seconds for SQL_CALC_FOUND_ROWS.
- 7+7 seconds in case when two queries used.

Filesort:
- 1.8 seconds for SQL_CALC_FOUND_ROWS.
- 1.8+0.05 seconds in case when two queries used.

So, obvious conclusion from this simple test is: when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.